<?php

namespace Acme\DemoBundle\Repository;

use Doctrine\ORM\EntityRepository;

class NhanVienRepository extends EntityRepository
{
	public function getTuoiDangMaxYear()	{
		$qb = $this->createQueryBuilder('p')
		->where('p.lydoid is null')
		->andwhere('p.ngayvaodang is not null');
		$qb->orderBy('p.ngayvaodang' , 'ASC')
		;
		$result = $qb->getQuery()->getResult();
		if(\Count($result)>0){
			return $result[0];
		}
		return null;
	}
	public function getTuoiDangMinYear()	{
		$qb = $this->createQueryBuilder('p')
		->where('p.lydoid is null');
		$qb->orderBy('p.ngayvaodang' , 'DESC')
		;
		$result = $qb->getQuery()->getResult();
		if(\Count($result)>0){
			return $result[0];
		}
		return null;
	}
	public function baoCaoTuoiDang($offset,$limit,$type,$from,$to)	{
	
		$date1 = new \DateTime('now');
		$date2 = new \DateTime('now');
	
		$qb = $this->createQueryBuilder('p')
		->where('p.lydoid is null');
		if($from >= 0) {
	
			if($type == "1"){
				$limit1 = $date1->modify('-'. $from .' year')->format("Y-m-d");
				$limit1 .= ' 00:00:00';
				$limit2 = $date2->modify('-'. ($from + 1) .' year')->format("Y-m-d");
				$limit2 .= ' 00:00:00';
				$qb->andWhere('p.ngayvaodang <= :ngayvaodang1')
				->andWhere('p.ngayvaodang > :ngayvaodang2')
				->setParameter('ngayvaodang1', $limit1)
				->setParameter('ngayvaodang2', $limit2);
			} elseif($type == "2"){
				$limit1 = $date1->$date1('-'.$from.' year')->format("Y-m-d");
				$limit1 .= ' 00:00:00';
				$qb->andWhere('p.ngayvaodang < :ngayvaodang1')
				->setParameter('ngayvaodang1', $limit1);
			}
			elseif($type == "3"){
				$limit1 = $date1->modify('-'.$from.' year')->format("Y-m-d");
				$limit1 .= ' 00:00:00';
				$qb->andWhere('p.ngayvaodang > :ngayvaodang1')
				->setParameter('ngayvaodang1', $limit1);
	
			}
			elseif($type == "4"){
				$limit1 = $date1->modify('-'. $from .' year')->format("Y-m-d");
				$limit1 .= ' 00:00:00';
				$limit2 = $date2->modify('-'. ($from + 1) .' year')->format("Y-m-d");
				$limit2 .= ' 00:00:00';
				$qb->andWhere('p.ngayvaodang <= :ngayvaodang1')
				->andWhere('p.ngayvaodang > :ngayvaodang2')
				->setParameter('ngayvaodang1', $limit1)
				->setParameter('ngayvaodang2', $limit2);
			}
		}
		$qb->orderBy('p.ngayvaodang' , 'ASC')
		->setFirstResult( $offset )
		->setMaxResults( $limit );
		return $qb->getQuery()->getResult();
	}
	public function countBaoCaoTuoiDang($type,$from,$to)	{
	
		$date1 = new \DateTime('now');
		$date2 = new \DateTime('now');
	
		$qb = $this->createQueryBuilder('p')
		->where('p.lydoid is null');
	if($from >= 0) {
	
			if($type == "1"){
				$limit1 = $date1->modify('-'. $from .' year')->format("Y-m-d");
				$limit1 .= ' 00:00:00';
				$limit2 = $date2->modify('-'. ($from + 1) .' year')->format("Y-m-d");
				$limit2 .= ' 00:00:00';
				$qb->andWhere('p.ngayvaodang <= :ngayvaodang1')
				->andWhere('p.ngayvaodang > :ngayvaodang2')
				->setParameter('ngayvaodang1', $limit1)
				->setParameter('ngayvaodang2', $limit2);
			} elseif($type == "2"){
				$limit1 = $date1->$date1('-'.$from.' year')->format("Y-m-d");
				$limit1 .= ' 00:00:00';
				$qb->andWhere('p.ngayvaodang < :ngayvaodang1')
				->setParameter('ngayvaodang1', $limit1);
			}
			elseif($type == "3"){
				$limit1 = $date1->modify('-'.$from.' year')->format("Y-m-d");
				$limit1 .= ' 00:00:00';
				$qb->andWhere('p.ngayvaodang > :ngayvaodang1')
				->setParameter('ngayvaodang1', $limit1);
	
			}
			elseif($type == "4"){
				$limit1 = $date1->modify('-'. $from .' year')->format("Y-m-d");
				$limit1 .= ' 00:00:00';
				$limit2 = $date2->modify('-'. ($from + 1) .' year')->format("Y-m-d");
				$limit2 .= ' 00:00:00';
				$qb->andWhere('p.ngayvaodang <= :ngayvaodang1')
				->andWhere('p.ngayvaodang > :ngayvaodang2')
				->setParameter('ngayvaodang1', $limit1)
				->setParameter('ngayvaodang2', $limit2);
			}
		}
		$qb->orderBy('p.ngayvaodang' , 'ASC');
		return $qb->getQuery()->getResult();
	}
	public function getMaxYear()	{
		$qb = $this->createQueryBuilder('p')
		->where('p.lydoid is null')
		->andwhere('p.ngayvaolam is not null');
		$qb->orderBy('p.ngayvaolam' , 'ASC')
		;
		$result = $qb->getQuery()->getResult();
		if(\Count($result)>0){
			return $result[0];
		}
		return null;
	}
	public function getMinYear()	{
		$qb = $this->createQueryBuilder('p')
		->where('p.lydoid is null');
		$qb->orderBy('p.ngayvaolam' , 'DESC')
		;
		$result = $qb->getQuery()->getResult();
		if(\Count($result)>0){
			return $result[0];
		}
		return null;
	}
	public function baoCaoNamCongTac($offset,$limit,$type,$from,$to)	{
	
		$date1 = new \DateTime('now');
		$date2 = new \DateTime('now');
	
		$qb = $this->createQueryBuilder('p')
		->where('p.lydoid is null');
	if($from >= 0) {
	
		if($type == "1"){
			$limit1 = $date1->modify('-'. $from .' year')->format("Y-m-d");
			$limit1 .= ' 00:00:00';
			$limit2 = $date2->modify('-'. ($from + 1) .' year')->format("Y-m-d");
			$limit2 .= ' 00:00:00';
			$qb->andWhere('p.ngayvaolam <= :ngayvaolam1')
			->andWhere('p.ngayvaolam > :ngayvaolam2')
			->setParameter('ngayvaolam1', $limit1)
			->setParameter('ngayvaolam2', $limit2);
		} elseif($type == "2"){
			$limit1 = $date1->$date1('-'.$from.' year')->format("Y-m-d");
			$limit1 .= ' 00:00:00';
			$qb->andWhere('p.ngayvaolam > :ngayvaolam1')
			->setParameter('ngayvaolam1', $limit1);
		}
		elseif($type == "3"){
			$limit1 = $date1->modify('-'.$from.' year')->format("Y-m-d");
			$limit1 .= ' 00:00:00';
			$qb->andWhere('p.ngayvaolam < :ngayvaolam1')
			->setParameter('ngayvaolam1', $limit1);
				
		}
		elseif($type == "4"){
			$limit1 = $date1->modify('-'. $from .' year')->format("Y-m-d");
			$limit1 .= ' 00:00:00';
			$limit2 = $date2->modify('-'. $to .' year')->format("Y-m-d");
			$limit2 .= ' 00:00:00';
			$qb->andWhere('p.ngayvaolam <= :ngayvaolam1')
			->andWhere('p.ngayvaolam >= :ngayvaolam2')
			->setParameter('ngayvaolam1', $limit1)
			->setParameter('ngayvaolam2', $limit2);
		}
	}
		$qb->orderBy('p.ngayvaolam' , 'ASC')
		->setFirstResult( $offset )
		->setMaxResults( $limit );
		return $qb->getQuery()->getResult();
	}
	public function countBaoCaoNamCongTac($type,$from,$to)	{
		
		$date1 = new \DateTime('now');
		$date2 = new \DateTime('now');
		
		$qb = $this->createQueryBuilder('p')
		->where('p.lydoid is null');
		if($from >= 0 ) {
		if($type == "1"){
			$limit1 = $date1->modify('-'. $from .' year')->format("Y-m-d");
			$limit1 .= ' 00:00:00';
			$limit2 = $date2->modify('-'. ($from + 1) .' year')->format("Y-m-d");
			$limit2 .= ' 00:00:00';
			$qb->andWhere('p.ngayvaolam <= :ngayvaolam1')
			->andWhere('p.ngayvaolam > :ngayvaolam2')
			->setParameter('ngayvaolam1', $limit1)
			->setParameter('ngayvaolam2', $limit2);
		} elseif($type == "2"){
			$limit1 = $date1->$date1('-'.$from.' year')->format("Y-m-d");
			$limit1 .= ' 00:00:00';
			$qb->andWhere('p.ngayvaolam > :ngayvaolam1')
			->setParameter('ngayvaolam1', $limit1);
		}
		elseif($type == "3"){
			$limit1 = $date1->modify('-'.$from.' year')->format("Y-m-d");
			$limit1 .= ' 00:00:00';
			$qb->andWhere('p.ngayvaolam < :ngayvaolam1')
			->setParameter('ngayvaolam1', $limit1);
				
		}
		elseif($type == "4"){
			$limit1 = $date1->modify('-'. $from .' year')->format("Y-m-d");
			$limit1 .= ' 00:00:00';
			$limit2 = $date2->modify('-'. $to .' year')->format("Y-m-d");
			$limit2 .= ' 00:00:00';
			$qb->andWhere('p.ngayvaolam <= :ngayvaolam1')
			->andWhere('p.ngayvaolam >= :ngayvaolam2')
			->setParameter('ngayvaolam1', $limit1)
			->setParameter('ngayvaolam2', $limit2);
		}
		}
		$qb->orderBy('p.ngayvaolam' , 'ASC');
		return $qb->getQuery()->getResult();
	}
	public function findByDate($from,$to)	{
	
		$qb = $this->createQueryBuilder('p')
		->where('p.lydoid is null');
		if($from != ""){
			$qb->andWhere('p.ngayvaolam >= :from')
			->setParameter('from', $from);
		}
		if($to != ""){
			$qb->andWhere('p.ngayvaolam <= :to')
			->setParameter('to', $to);
		}
	
		$qb->orderBy('p.id' , 'DESC');
		return $qb->getQuery()->getResult();
	}
	public function findForBaoCaoNhanLuc($offset,$limit,$type)	{
		$dateNow = new \DateTime('now');
		$limit1 = $dateNow->modify('-30 year')->format("Y");
		$limit1 .= '-01-01 00:00:00';
		$limit2 = $dateNow->modify('-40 year')->format("Y");
		$limit2 .= '-01-01 00:00:00';
		$limit3 = $dateNow->modify('-50 year')->format("Y");
		$limit3 .= '-01-01 00:00:00';
		$limit4 = $dateNow->modify('-60 year')->format("Y");
		$limit4 .= '-01-01 00:00:00';
		$qb = $this->createQueryBuilder('p')
		->where('p.lydoid is null');
		if($type == "1"){
			
			$qb->andWhere('p.ngaysinh >= :ngaysinh1')
			->setParameter('ngaysinh1', $limit1);
		} elseif($type == "2"){
			$qb->andWhere('p.ngaysinh <= :ngaysinh1')
			->setParameter('ngaysinh1', $limit1)
			->andWhere('p.ngaysinh >= :ngaysinh2')
			->setParameter('ngaysinh2', $limit2);
		}
		elseif($type == "3"){
			$qb->andWhere('p.ngaysinh <= :ngaysinh2')
			->setParameter('ngaysinh2', $limit2)
			->andWhere('p.ngaysinh >= :ngaysinh3')
			->setParameter('ngaysinh3', $limit3);
			
		}
		elseif($type == "4"){
			$qb->andWhere('p.ngaysinh <= :ngaysinh3')
			->setParameter('ngaysinh3', $limit3);
			
		}
		
		$qb->orderBy('p.id' , 'DESC')
		->setFirstResult( $offset )
		->setMaxResults( $limit );
		return $qb->getQuery()->getResult();
	}
	public function countAllForBaoCaoNhanLuc($type)	{
	
		$dateNow = new \DateTime('now');
		$limit1 = $dateNow->modify('-30 year')->format("Y");
		$limit1 .= '-01-01 00:00:00';
		$limit2 = $dateNow->modify('-40 year')->format("Y");
		$limit2 .= '-01-01 00:00:00';
		$limit3 = $dateNow->modify('-50 year')->format("Y");
		$limit3 .= '-01-01 00:00:00';
		$limit4 = $dateNow->modify('-60 year')->format("Y");
		$limit4 .= '-01-01 00:00:00';
		$qb = $this->createQueryBuilder('p')
		->where('p.lydoid is null');
		if($type == "1"){
			
			$qb->andWhere('p.ngaysinh >= :ngaysinh1')
			->setParameter('ngaysinh1', $limit1);
		} elseif($type == "2"){
			$qb->andWhere('p.ngaysinh <= :ngaysinh1')
			->setParameter('ngaysinh1', $limit1)
			->andWhere('p.ngaysinh >= :ngaysinh2')
			->setParameter('ngaysinh2', $limit2);
		}
		elseif($type == "3"){
			$qb->andWhere('p.ngaysinh <= :ngaysinh2')
			->setParameter('ngaysinh2', $limit2)
			->andWhere('p.ngaysinh >= :ngaysinh3')
			->setParameter('ngaysinh3', $limit3);
			
		}
		elseif($type == "4"){
			$qb->andWhere('p.ngaysinh <= :ngaysinh3')
			->setParameter('ngaysinh3', $limit3);
			
		}
		return $qb->getQuery()->getResult();
	}
	public function findByCoditions($offset,$limit,$filter)	{
		
		$qb = $this->createQueryBuilder('p')
		->where('1 = 1');
		if($filter['hoten'] != ""){
			$qb->andWhere('p.hoten like :hoten')
			->setParameter('hoten', '%' . $filter['hoten'] . '%');
		}
		if($filter['chucvuid'] != ""){
			$qb->andWhere('p.chucvuid = :chucvuid')
			->setParameter('chucvuid', $filter['chucvuid']);
		}
		if($filter['phongbanid'] != ""){
			$qb->andWhere('p.phongbanid = :phongbanid')
			->setParameter('phongbanid', $filter['phongbanid']);
		}
		if($filter['trinhdoid'] != ""){
			$qb->andWhere('p.trinhdoid = :trinhdoid')
			->setParameter('trinhdoid', $filter['trinhdoid']);
		}
		if($filter['lydoid'] == "1"){
			$qb->andWhere('p.lydoid is null');
		} else if ($filter['lydoid'] == "3"){
			$qb->andWhere('p.lydoid is not null');
		}
		$qb->orderBy('p.thutu' , 'ASC')
		->setFirstResult( $offset )
		->setMaxResults( $limit );
		return $qb->getQuery()->getResult();
	}
	public function findByPhongBanId($id)	{
	
		$qb = $this->createQueryBuilder('p')
		->where('p.phongbanid = :phongbanid')
		->setParameter('phongbanid', $id);
		$qb->orderBy('p.thutu' , 'ASC');
		return $qb->getQuery()->getResult();
	}
	public function countAll($filter)	{
	
		$qb = $this->createQueryBuilder('p')->where('1 = 1');
	
		if($filter['hoten'] != ""){
			$qb->andWhere('p.hoten like :hoten')
			->setParameter('hoten', '%' . $filter['hoten'] . '%');
		}
		if($filter['chucvuid'] != ""){
			$qb->andWhere('p.chucvuid = :chucvuid')
			->setParameter('chucvuid', $filter['chucvuid']);
		}
		if($filter['phongbanid'] != ""){
			$qb->andWhere('p.phongbanid = :phongbanid')
			->setParameter('phongbanid', $filter['phongbanid']);
		}
		if($filter['trinhdoid'] != ""){
			$qb->andWhere('p.trinhdoid = :trinhdoid')
			->setParameter('trinhdoid', $filter['trinhdoid']);
		}
		if($filter['lydoid'] == "1"){
			$qb->andWhere('p.lydoid is null');
		} else if ($filter['lydoid'] == "3"){
			$qb->andWhere('p.lydoid is not null');
		}
		return $qb->getQuery()->getResult();
	}
	public function saveNhanVien($NhanVien) {
		$em = $this->getEntityManager();
		$em->persist($NhanVien);
		$em->flush();
	}
	public function checkMaInsert($ma)	{
	
		$qb = $this->createQueryBuilder('p')
		->where('p.deleted is null')
		->andWhere('p.ma = :ma')
		->setParameter('ma', $ma);
	
		return $qb->getQuery()->getResult();
	}
	
	public function checkMaUpdate($ma ,$macu)	{
	
		$qb = $this->createQueryBuilder('p')
		->where('p.deleted is null')
		->andWhere('p.ma = :ma')
		->andWhere('p.ma != :macu')
		->setParameter('ma', $ma)
		->setParameter('macu', $macu);
	
		return $qb->getQuery()->getResult();
	}
	
	public function findForBaoCaoTrinhDo($offset,$limit,$trinhdo)	{
		
		$qb = $this->createQueryBuilder('p')
		->where('p.lydoid is null');
		if($trinhdo > 0){
			$qb->andWhere('p.trinhdoid = :trinhdo')
			->setParameter('trinhdo', $trinhdo);
		}
		$qb->orderBy('p.thutu' , 'ASC')
		->setFirstResult( $offset )
		->setMaxResults( $limit );
		return $qb->getQuery()->getResult();
	}
	public function countAllForBaoCaoTrinhDo($trinhdo)	{
	
		$qb = $this->createQueryBuilder('p')
		->where('p.lydoid is null');
		if($trinhdo > 0){
			$qb->andWhere('p.trinhdoid = :trinhdo')
			->setParameter('trinhdo', $trinhdo);
		}
		$qb->orderBy('p.thutu' , 'ASC');
		return $qb->getQuery()->getResult();
	}
	public function getAll()	{
		$qb = $this->createQueryBuilder('p')
		->where('p.lydoid is null');
		$qb->orderBy('p.thutu' , 'ASC');
		return $qb->getQuery()->getResult();
	}
}